#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
生成数据质量监控报表模板的SQL插入语句
"""
import json

# 定义param_schema
param_schema = {
    "type": "object",
    "properties": {
        "source_tables": {
            "type": "source_table_config",
            "format": "source_table_config",
            "title": "源表配置",
            "description": "配置多个源表，系统将自动合并统计。点击\"添加源表\"按钮可以添加多个源表。"
        },
        "target_table_config": {
            "type": "target_table_config",
            "format": "target_table_config",
            "title": "目标表配置",
            "description": "配置目标表信息，用于统计同步率和数据状态"
        },
        "filter_rules": {
            "type": "object",
            "title": "过滤规则",
            "description": "配置平台过滤规则",
            "properties": {
                "platform": {
                    "type": "string",
                    "title": "平台",
                    "description": "平台标识，用于SQL WHERE条件过滤，如：dy（抖音）、tb（淘宝）等",
                    "default": "dy"
                },
                "platform_summary": {
                    "type": "string",
                    "title": "平台汇总",
                    "description": "平台汇总显示名称，用于报表显示，如：抖音、抖音平台等（留空则使用平台标识）",
                    "default": ""
                },
                "sync_rate_threshold": {
                    "type": "integer",
                    "title": "同步率阈值",
                    "description": "同步率低于此阈值时，数据状态显示为\"同步率过低\"",
                    "default": 80,
                    "minimum": 0,
                    "maximum": 100
                }
            },
            "required": ["platform"]
        },
        "display_fields": {
            "type": "display_fields",
            "format": "display_fields",
            "title": "显示字段",
            "description": "选择要在报表中显示的字段",
            "options": [
                {"value": "platform", "label": "平台"},
                {"value": "platform_summary", "label": "平台汇总"},
                {"value": "source_layer", "label": "原始层(源表)"},
                {"value": "source_total_count", "label": "原始层商品总数"},
                {"value": "dws_total_count", "label": "目标表商品总数"},
                {"value": "dws_on_sale_count", "label": "目标表上架商品数"},
                {"value": "dws_off_sale_count", "label": "目标表下架商品数"},
                {"value": "sync_rate", "label": "同步率"},
                {"value": "data_status", "label": "数据状态"}
            ],
            "default": [
                "platform",
                "source_layer",
                "source_total_count",
                "dws_total_count",
                "dws_on_sale_count",
                "dws_off_sale_count",
                "sync_rate",
                "data_status"
            ]
        }
    },
    "required": ["source_tables", "target_table_config", "filter_rules"]
}

# 定义default_params
default_params = {
    "source_tables": {
        "data_source_id": None,
        "databases": [
            {
                "database_name": "",
                "table_names": [],
                "tables": [
                    {
                        "table_name": "",
                        "distinct_field": "product_id",
                        "time_field": "etl_date"
                    }
                ]
            }
        ],
        "time_range_type": "all",
        "time_start": None,
        "time_end": None
    },
    "target_table_config": {
        "data_source_id": None,
        "database_name": "",
        "table_name": "",
        "join_field": "product_id",
        "filter_field": "platform",
        "filter_value": "dy",
        "stat_fields": [],
        "time_field": "etl_date",
        "time_range_type": "all",
        "time_start": None,
        "time_end": None
    },
    "stat_field_mappings": {
        "is_on_sales": {
            "output_fields": ["dws_on_sale_count", "dws_off_sale_count"],
            "conditions": ["=0", "=1"],
            "description": "商品上架状态统计：is_on_sales=0表示上架，=1表示下架"
        }
    },
    "field_labels": {
        "platform": "平台",
        "platform_summary": "平台汇总",
        "source_layer": "原始层(源表)",
        "source_total_count": "原始层商品总数",
        "dws_total_count": "目标表商品总数",
        "dws_on_sale_count": "目标表上架商品数",
        "dws_off_sale_count": "目标表下架商品数",
        "sync_rate": "同步率",
        "data_status": "数据状态"
    },
    "filter_rules": {
        "platform": "dy",
        "platform_summary": "抖音",
        "sync_rate_threshold": 80
    },
    "display_fields": [
        "platform",
        "source_layer",
        "source_total_count",
        "dws_total_count",
        "dws_on_sale_count",
        "dws_off_sale_count",
        "sync_rate",
        "data_status"
    ],
    "sql_template": """/*
 * 报表名称: 数据质量监控报表
 * 描述: 统计源表到目标表的同步情况
 * 功能:
 *   1. 合并源表的product_id,统一去重统计作为原始层总数
 *   2. 统计目标表中来自源表的商品数
 *   3. 计算同步率和数据状态
 */

-- =============================================
-- 第一部分: 合并源表的product_id,统一去重统计
-- =============================================
WITH source_union AS (
    SELECT COUNT(DISTINCT product_id) AS source_total_count
    FROM (
        {source_union_sql}
    ) t
),

-- =============================================
-- 第二部分: 获取合并后的product_id集合
-- 说明: 用于判断目标表中哪些是来自源表的数据
-- =============================================
source_product_ids AS (
    SELECT DISTINCT product_id
    FROM (
        {source_product_ids_sql}
    ) t
),

-- =============================================
-- 第三部分: 统计目标表中来自源表的数据
-- =============================================
target_stats AS (
    SELECT
        {stat_select_clause}
    FROM {target_table_ref} target
    WHERE EXISTS (
        SELECT 1
        FROM source_product_ids src
        WHERE src.product_id = target.{target_join_field}
    ){where_clause}
)

-- =============================================
-- 第四部分: 输出汇总结果
-- =============================================
SELECT
    '{platform}' AS '{platform_label}',
    '{platform_summary}' AS '{platform_summary_label}',
    '源表合并去重' AS '{source_layer_label}',
    src.source_total_count AS '{source_total_count_label}',
    COALESCE(target.dws_total_count, 0) AS '{dws_total_count_label}',
    COALESCE(target.dws_on_sale_count, 0) AS '{dws_on_sale_count_label}',
    COALESCE(target.dws_off_sale_count, 0) AS '{dws_off_sale_count_label}',
    CONCAT(
        ROUND(
            CASE
                WHEN src.source_total_count > 0
                THEN (COALESCE(target.dws_total_count, 0) * 100.0 / src.source_total_count)
                ELSE 0
            END,
            2
        ),
        '%'
    ) AS '{sync_rate_label}',
    CASE
        WHEN src.source_total_count = 0 THEN '无原始数据'
        WHEN COALESCE(target.dws_total_count, 0) = 0 THEN '未同步'
        WHEN (COALESCE(target.dws_total_count, 0) * 100.0 / src.source_total_count) < {sync_rate_threshold} THEN '同步率过低'
        ELSE '正常'
    END AS '{data_status_label}'
FROM source_union src, target_stats target;"""
}

# 转换为JSON字符串
param_schema_json = json.dumps(param_schema, ensure_ascii=False)
default_params_json = json.dumps(default_params, ensure_ascii=False)

# 转义用于MySQL单引号字符串：
# 在MySQL单引号字符串中：
# - 单引号需要双倍: ' -> ''
# - 反斜杠需要转义: \ -> \\
# - 对于JSON字符串，需要确保反斜杠被正确转义
def escape_for_mysql(json_str):
    # 按顺序转义，避免重复转义
    # 1. 先转义反斜杠（必须在最前面，避免影响其他转义）
    result = json_str.replace('\\', '\\\\')
    # 2. 转义单引号（必须在反斜杠之后）
    result = result.replace("'", "''")
    # 注意：JSON字符串中的 \n, \t, \r 等已经被 json.dumps() 正确处理为 \\n, \\t, \\r
    # 经过步骤1的反斜杠转义后，它们会变成 \\\\n, \\\\t, \\\\r，这是正确的
    return result

param_schema_escaped = escape_for_mysql(param_schema_json)
default_params_escaped = escape_for_mysql(default_params_json)

# 生成SQL语句
sql = f"""-- ============================================
-- 插入CPS源表对比目标表统计报表模板
-- ============================================
-- 说明：CPS业务数据质量监控报表模板，支持多源表合并统计和目标表（DWS层）数据对比分析

INSERT INTO report_templates 
(template_code, template_name, template_group, description, param_schema, default_params, output_formats, is_active, version)
VALUES 
(
    'data_quality_monitor', 
    '数据质量监控报表', 
    'mixed', 
    '统计cps平台数据从TDS/ODS到DWS的同步情况,进行数据对比分析，计算同步率、上架/下架商品数等关键指标，帮助监控数据同步质量和业务数据完整性',
    '{param_schema_escaped}',
    '{default_params_escaped}',
    '["html", "excel"]',
    1,
    1
);

-- 验证插入结果
SELECT 
    template_code,
    template_name,
    template_group,
    description,
    is_active,
    version,
    created_at
FROM report_templates
WHERE template_code = 'data_quality_monitor';
"""

print(sql)
